import pymysql
from pymysql.constants import ER
# pip3 install PyMySQL

from view.endSrc.DBConfig import DBConfig


from view.endSrc.MyLogger import MyLogger

class MySqlConn:

    # http://mysql-python.sourceforge.net/MySQLdb-1.2.2/
    def __init__(self, config: DBConfig, timezoneStr='+08:00'):
        self.db = None
        self.logger = config.logger
        self.cfg = config
        self.timezoneStr = timezoneStr   # like '+8:00', '+08:00', '-04:00'
        # mysql> set @@session.time_zone="+08:00";
    def close(self):
        if self.db:
            self.db.close()
            self.db = None

    def open(self):
        try:
            self.close()
            self.db = pymysql.connect(host=self.cfg.host,
                                      port=self.cfg.port,
                                      passwd=self.cfg.passwd,
                                      db=self.cfg.db,
                                      user=self.cfg.user)

            print("opened...", self.cfg.db)

        except Exception as e:
            self.logger.write('MySqlConn..open failed: ' + str(e))
            self.db = None
            return False
        if not self.db:
            return False

        # set timezone
        try:
            cursor = self.db.cursor()
            # execute SQL query using execute() method.
            # Returns long integer rows affected, if any
            # set @@session.time_zone = "+08:00";
            cmd = "set @@session.time_zone=" + "'" + self.timezoneStr+ "'"
            nbrows = cursor.execute(cmd)
            print('nbrows: ', nbrows)   # nbrows: 0
            return True
        except Exception as e:
            self.logger.write('MySqlConn.open..exec cmd=' + cmd +
                              ' failed..return False')
            self.close()
        return False

    def getVersion(self):
        i = 0
        while i< 3:
            i += 1
            if not self.db:
                self.open()
                continue
            try:
                cursor = self.db.cursor()
                # execute SQL query using execute() method.
                # Returns long integer rows affected, if any
                nbrows = cursor.execute("SELECT VERSION()")
                if nbrows == 0:
                    return None
                # Fetch a single row using fetchone() method.
                data = cursor.fetchone()
                # print("Database version :", data)
                cursor.close()
                return data
            except Exception as e:
                self.logger.write('MySqlConn..getVersion failed for ' + str(i) +
                                  ' times: '+ str(e))
                self.open()
        return None

    def getSessionTimeZone(self):
        i = 0
        while i< 3:
            i += 1
            if not self.db:
                self.open()
                continue
            try:
                cursor = self.db.cursor()
                # execute SQL query using execute() method.
                # Returns long integer rows affected, if any
                nbrows = cursor.execute("SELECT @@session.time_zone")
                if nbrows == 0:
                    return None
                # Fetch a single row using fetchone() method.
                data = cursor.fetchone()
                cursor.close()
                return data
            except Exception as e:
                self.logger.write('MySqlConn..getSessionTimeZone failed for ' + str(i) +
                                  ' times: '+ str(e))
                self.open()
        return None

    def createTable(self, createSql=None):
        i = 0
        while i< 3:
            i += 1
            if not self.db:
                self.open()
                continue
            try:
                # prepare a cursor object using cursor() method
                cursor = self.db.cursor()
                if createSql:
                    # Drop table if it already exist using execute() method.
                    cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
                    nrows = cursor.execute(createSql)
                #     Warning: (1051, "Unknown table 'EMPLOYEE'")

                # Create table as per requirement
                # sql = """CREATE TABLE EMPLOYEE (
                #      FIRST_NAME  CHAR(20) NOT NULL,
                #      LAST_NAME  CHAR(20),
                #      AGE INT,
                #      SEX CHAR(1),
                #      INCOME FLOAT )"""
                #cursor.execute(createSql)
                # disconnect from server
                cursor.close()
                return True
            except Exception as e:
                self.logger.write('MySqlConn..createTable failed for ' + str(i) +
                                  ' times: ' + str(e) + '\n' + createSql)
                self.open()
        return False


    def insertRetId(self, sql):

        sql = sql.strip()   # remove prefix/suffix spaces.
        tmp = sql.upper()
        assert tmp.startswith('INSERT'), 'MySqlConn: can only exec insert statement: ' + sql

        i = 0
        while i < 3:
            i += 1
            if not self.db:
                self.open()
                continue
            try:
                # prepare a cursor object using cursor() method
                cursor = self.db.cursor()

                # # Prepare SQL query to INSERT a record into the database.
                # sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
                #    LAST_NAME, AGE, SEX, INCOME) \
                #    VALUES ('%s', '%s', '%d', '%c', '%d' )" % \
                #   ('Mac', 'Mohan', 20, 'M', 2000)

                # Execute the SQL command
                cursor.execute(sql + ';')

                cursor.execute("SELECT LAST_INSERT_ID();")
                newId = cursor.fetchone()[0]
                # Commit your changes in the database
                self.db.commit()
                return newId
            except Exception as e:
                self.logger.write('insertRetId.. failed for ' + str(i) +
                                  ' times: ' + str(e) + '\n' + sql)
                # Rollback in case there is any error
                self.db.rollback()
                self.open()
            return None

    # update, insert, delete
    def insDelUpd(self, sql):
        i = 0
        while i< 3:
            i += 1
            if not self.db:
                self.open()
                continue
            try:
                # prepare a cursor object using cursor() method
                cursor = self.db.cursor()

                # # Prepare SQL query to INSERT a record into the database.
                # sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
                #    LAST_NAME, AGE, SEX, INCOME) \
                #    VALUES ('%s', '%s', '%d', '%c', '%d' )" % \
                #   ('Mac', 'Mohan', 20, 'M', 2000)
                # sql = "UPDATE EMPLOYEE SET AGE = AGE + 1
                # WHERE  SEX = '%c'" % ('M')
                # sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)

                # Execute the SQL command
                cursor.execute(sql)
                # Commit your changes in the database
                self.db.commit()
                return True
            except Exception as e:
                self.logger.write('insDelUpd.. failed for ' + str(i) +
                                  ' times: ' + str(e) + '\n' + sql)
                # Rollback in case there is any error
                self.db.rollback()
                self.open()
            return False

    def read(self, sql):
        i = 0
        while i< 3:
            i += 1
            if not self.db:
                self.open()
                continue
            try:
                # Execute the SQL command
                cursor = self.db.cursor()
                cursor.execute(sql)
                # Fetch all the rows in a list of lists.
                results = cursor.fetchall()
                # for row in results:
                #     fname = row[0]
                #     lname = row[1]
                #     age = row[2]
                #     sex = row[3]
                #     income = row[4]
                #     # Now print fetched result
                #     print
                #     "fname=%s,lname=%s,age=%d,sex=%s,income=%d" % \
                #     (fname, lname, age, sex, income)
                #                              income should be type of integer
                #     sql = """CREATE TABLE EMPLOYEE (
                #              FIRST_NAME  CHAR(20) NOT NULL,
                #              LAST_NAME  CHAR(20),
                #              AGE INT,
                #              SEX CHAR(1),
                #              INCOME FLOAT )"""

                # returresults) == 0:
                #     return None         # yjf.2020.8.1 empty equal to None WRONG!!!
                return results
            except Exception as e:
                self.logger.write('read..failed for ' + str(i) +
                                  ' times: ' + str(e))
                self.open()
        return None

    def tableExists(self, tableName):

        sql = "SELECT 1 FROM " + tableName + " LIMIT 1"
        i = 0
        while i< 3:
            i += 1
            if not self.db:
                self.open()
                continue
            try:
                cursor = self.db.cursor()
                cursor.execute(sql)
                return True
            except Exception as e:
                self.logger.write('isTableExist..failed for ' + str(i) +
                                  ' times: ' + str(e))
                if isinstance(e, pymysql.ProgrammingError):
                    if e.args[0] == ER.NO_SUCH_TABLE:
                        return False
                self.open()
        return False

    def dropTable(self, tableName):

        if not self.tableExists(tableName):
            return True

        i = 0
        while i< 3:
            i += 1
            if not self.db:
                self.open()
                continue
            try:
                # prepare a cursor object using cursor() method
                cursor = self.db.cursor()
                # DROP TABLE table_name ;
                dropSql = "DROP TABLE " + tableName
                cursor.execute(dropSql)
                # disconnect from server
                cursor.close()
                return True
            except Exception as e:
                self.logger.write('MySqlConn..dropTable failed for ' + str(i) +
                                  ' times: ' + str(e) + '\n' + dropSql)
                self.open()
        return False


####################################################################
####################################################################
def testCreateTable():
    conf = DBConfig()
    con = MySqlConn(conf)
    ok = con.open()
    if ok:
        ver = con.getVersion()
        print('ver: ', ver)
        con.close()

    sql = """CREATE TABLE EMPLOYEE (
             id  INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
             FIRST_NAME  CHAR(20) NOT NULL,
             LAST_NAME  CHAR(20),
             AGE INT,
             SEX CHAR(1),
             INCOME DOUBLE )"""
    ok = con.createTable(sql)
    if not ok:
        print('.....failed..create table')


def testInsertRow(rowTuple):

    conf = DBConfig()
    con = MySqlConn(conf)

    sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
       LAST_NAME, AGE, SEX, INCOME) \
       VALUES ('%s', '%s', '%d', '%c', '%d' )" % rowTuple


    ok = con.insDelUpd(sql)
    if not ok:
        print('.....failed...insert')

def testInsertRow_RetId():

    conf = DBConfig()
    con = MySqlConn(conf)

    rowTuple = ('Richard', 'Stall', 18, 'm', 120.1)
    sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
       LAST_NAME, AGE, SEX, INCOME) \
       VALUES ('%s', '%s', %d, '%c', %f )" % rowTuple


    # ok = con.insDelUpd(sql)
    ok = con.insertRetId(sql)
    if ok is None:
        print('.....failed...insert')
    else:
        print('ok: retId: ', ok)

def testReadRows():
    conf = DBConfig()
    con = MySqlConn(conf)

    sql = "SELECT * FROM EMPLOYEE"
    rows = con.read(sql)
    print(rows)
    for r in rows:
        print("id = %d,fname=%s,lname=%s,age=%d,sex=%s,income=%0.2f" % \
                (r[0], r[1], r[2], r[3], r[4], r[5]))


def testTableExists():
    conf = DBConfig()
    con = MySqlConn(conf)
    res = con.tableExists('testtable')
    if not res:
        print('testtable...does not exist')
    res = con.tableExists('EMPLOYEE')
    if res:
        print('EMPLOYEE exists')




if __name__ == '__main__':
    testCreateTable()   #ok
    #testInsertRow_RetId()   #ok

    #rtup = ('Mac', 'Mohan', 19, 'M', 1200) #ok
    #testInsertRow(rtup)   #ok

    #testReadRows()   #ok

    #testTableExists()   #Ok
    testTableExists()   #Ok
